package com.imooc.jdbc.smaple;

import com.imooc.jdbc.common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class BatchSample {

    private static void tc1(){
        Connection conn = null;
        PreparedStatement preStmt = null;


        try {

            long startTime = new Date().getTime();


            conn = DbUtils.getConnection();
            conn.setAutoCommit(false);
            String insertSql = "INSERT INTO employee (dep_name,emp_name,salary) VALUES (?,?,?)";


            for(int i=100000; i< 200000; i++){
                preStmt = conn.prepareStatement(insertSql);
                preStmt.setString(1, "研发部");
                preStmt.setString(2, "员工" + i);
                preStmt.setFloat(3, 3500);

                preStmt.executeUpdate();
            }

            conn.commit();
            long endTime = new Date().getTime();
            System.out.println("员工批量导入成功");
            System.out.println("tc1执行时长：" + (endTime - startTime));
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn !=null && !conn.isClosed()) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, preStmt, conn);
        }
    }


    private static void tc2(){
        Connection conn = null;
        PreparedStatement preStmt = null;

        try {

            long startTime = new Date().getTime();

            conn = DbUtils.getConnection();
            conn.setAutoCommit(false);
            String insertSql = "INSERT INTO employee (dep_name,emp_name,salary) VALUES (?,?,?)";
            preStmt = conn.prepareStatement(insertSql);

            for(int i=200000; i< 300000; i++){
                preStmt.setString(1, "研发部");
                preStmt.setString(2, "员工" + i);
                preStmt.setFloat(3, 3500);

//                preStmt.executeUpdate();
                preStmt.addBatch(); // 将参数加入到批处理任务
            }

            preStmt.executeBatch(); //执行批处理

            conn.commit();
            long endTime = new Date().getTime();
            System.out.println("员工批量导入成功");
            System.out.println("tc2执行时长：" + (endTime - startTime));
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn !=null && !conn.isClosed()) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, preStmt, conn);
        }
    }


    public static void main(String[] args) {


tc1();
tc2();

    }
}
